package top.lbqaq.dao;

import top.lbqaq.pojo.Product;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 * @author Gor
 */
public class ProductDao {

    public List<Product> getAllCategory1(Connection connection,String productCategory1) throws SQLException {
        Product product;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Product> products = new LinkedList<>();
        if (connection != null) {
            String sql = "select * from product where product_category1 = ? ";
            Object[] params = {productCategory1};
            rs = BaseDao.execute(connection, pstm, rs, sql,params);
            while (rs.next()) {
                product = new Product();
                product.setProductCategory1(rs.getString("product_category1"));
                product.setProductCategory2(rs.getString("product_category2"));
                product.setProductNo(rs.getString("product_No"));
                product.setProductName(rs.getString("product_name"));
                product.setProductUnit(rs.getString("product_unit"));
                product.setProductPicture(rs.getString("product_picture"));
                product.setProductRemark(rs.getString("product_remark"));
                 product.setId(rs.getInt("id"));
                products.add(product);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return products;

    }
    public Product getAllById(Connection connection, int id) throws SQLException {
        Product product = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        //List<Product> products = new LinkedList<>();
        if (connection != null) {
            String sql = "select * from product where id = ? ";
            Object[] params = {id};
            rs = BaseDao.execute(connection, pstm, rs, sql,params);
            while (rs.next()) {
                product = new Product();
                product.setProductCategory1(rs.getString("product_category1"));
                product.setProductCategory2(rs.getString("product_category2"));
                product.setProductNo(rs.getString("product_No"));
                product.setProductName(rs.getString("product_name"));
                product.setProductUnit(rs.getString("product_unit"));
                product.setProductPicture(rs.getString("product_picture"));
                product.setProductRemark(rs.getString("product_remark"));
                product.setId(rs.getInt("id"));
               // products.add(product);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return product;

    }
    public List<Product> getAllCategory2(Connection connection,String productCategory2) throws SQLException {
        Product product;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Product> products = new LinkedList<>();
        if (connection != null) {
            String sql = "select * from product where product_category2 = ? ";
            Object[] params = {productCategory2};
            rs = BaseDao.execute(connection, pstm, rs, sql,params);
            while (rs.next()) {
                product = new Product();
                product.setProductCategory1(rs.getString("product_category1"));
                product.setProductCategory2(rs.getString("product_category2"));
                product.setProductNo(rs.getString("product_No"));
                product.setProductName(rs.getString("product_name"));
                product.setProductUnit(rs.getString("product_unit"));
                product.setProductPicture(rs.getString("product_picture"));
                product.setProductRemark(rs.getString("product_remark"));
                product.setId(rs.getInt("id"));
                products.add(product);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return products;

    }
    public List<Product> getAllByName(Connection connection,String name) throws SQLException {
        Product product;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Product> products = new LinkedList<>();
        if (connection != null) {
            String sql = "select * from product where product_name = ? ";
            Object[] params = {name};
            rs = BaseDao.execute(connection, pstm, rs, sql,params);
            while (rs.next()) {
                product = new Product();
                product.setProductCategory1(rs.getString("product_category1"));
                product.setProductCategory2(rs.getString("product_category2"));
                product.setProductNo(rs.getString("product_No"));
                product.setProductName(rs.getString("product_name"));
                product.setProductUnit(rs.getString("product_unit"));
                product.setProductPicture(rs.getString("product_picture"));
                product.setProductRemark(rs.getString("product_remark"));
                product.setId(rs.getInt("id"));
                products.add(product);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return products;

    }
    public int delectAllByNo(Connection connection,String No) throws SQLException {
        Product product;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int n=0;
        List<Product> products = new LinkedList<>();
        if (connection != null) {
            String sql = "delete from product where product_No = ? ";
            Object[] params = {No};
          n = BaseDao.execute(connection, pstm, sql,params);
            BaseDao.closeResource(null, pstm, rs);
        }
        return n;

    }
    public int updateAllById(Connection connection,Product product) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int n=0;
        if (connection != null) {

            String sql = "update product set product_category1=?,product_category2=?,product_No=?,product_name=?,product_unit=?,product_picture=?,product_remark=? where id = ?";
            Object[] params = {product.getProductCategory1(),product.getProductCategory2(),product.getProductNo(),product.getProductName(),
                    product.getProductUnit(),product.getProductPicture(),product.getProductRemark(),product.getId()};
             n = BaseDao.execute(connection, pstm, sql,params);
            BaseDao.closeResource(null, pstm, rs);
        }
        return n;

    }
    public int insert(Connection connection,Product product) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int n=0;
        if (connection != null) {

            String sql = "insert into product (product_category1,product_category2,product_No,product_name,product_unit" +
                    ",product_picture,product_remark) values(?,?,?,?,?,?,?)";
            Object[] params = {product.getProductCategory1(),product.getProductCategory2(),product.getProductNo(),product.getProductName(),
                    product.getProductUnit(),product.getProductPicture(),product.getProductRemark()};
            n = BaseDao.execute(connection, pstm, sql,params);
            BaseDao.closeResource(null, pstm, rs);
        }
        return n;

    }
    public List<Product> getAllProduct(Connection connection) throws SQLException {
        Product product;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Product> products = new LinkedList<>();
        if (connection != null) {
            String sql = "select * from product";
            rs = BaseDao.execute(connection, pstm, rs, sql);
            while (rs.next()) {
                product = new Product();
                product.setProductCategory1(rs.getString("product_category1"));
                product.setProductCategory2(rs.getString("product_category2"));
                product.setProductNo(rs.getString("product_No"));
                product.setProductName(rs.getString("product_name"));
                product.setProductUnit(rs.getString("product_unit"));
                product.setProductPicture(rs.getString("product_picture"));
                product.setProductRemark(rs.getString("product_remark"));
                product.setId(rs.getInt("id"));
                // System.out.println(product.getId());
                // product.setId(rs.getInt("id"));
                products.add(product);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return products;

    }
    public List getAllProductpg(int startIndex, int pagesize, Connection connection) throws SQLException{
        Product product;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Product> products = new LinkedList<>();
        if (connection != null) {
            String sql = "select * from product limit ?,?";
            Object[] params = {startIndex,pagesize};

                    rs = BaseDao.execute(connection, pstm, rs, sql,params);
            while (rs.next()) {
                product = new Product();
                product.setProductCategory1(rs.getString("product_category1"));
                product.setProductCategory2(rs.getString("product_category2"));
                product.setProductNo(rs.getString("product_No"));
                product.setProductName(rs.getString("product_name"));
                product.setProductUnit(rs.getString("product_unit"));
                product.setProductPicture(rs.getString("product_picture"));
                product.setProductRemark(rs.getString("product_remark"));
                product.setId(rs.getInt("id"));
                // System.out.println(product.getId());
                // product.setId(rs.getInt("id"));
                products.add(product);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return products;
    }
    public List<Product> getAllCategory1pg(Connection connection,String productCategory1,int startIndex, int pagesize) throws SQLException {
        Product product;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Product> products = new LinkedList<>();
        if (connection != null) {
            String sql = "select * from product where product_category1 = ? limit ?,?";
            Object[] params = {productCategory1,startIndex,pagesize};
            rs = BaseDao.execute(connection, pstm, rs, sql,params);
            while (rs.next()) {
                product = new Product();
                product.setProductCategory1(rs.getString("product_category1"));
                product.setProductCategory2(rs.getString("product_category2"));
                product.setProductNo(rs.getString("product_No"));
                product.setProductName(rs.getString("product_name"));
                product.setProductUnit(rs.getString("product_unit"));
                product.setProductPicture(rs.getString("product_picture"));
                product.setProductRemark(rs.getString("product_remark"));
                product.setId(rs.getInt("id"));
                products.add(product);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return products;

    }
}
